package com.cskaoyan.batch;

import com.cskaoyan.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/08/26 09:46
 *
 * @author ciggar
 */
public class BatchDemo {

    // 声明连接对象
    static Connection connection;

    static {
        connection = JDBCUtils.getConnection();
    }



    // 批处理相关

    public static void main(String[] args) throws SQLException {

        int n = 1000000;

//        long t1 = System.currentTimeMillis();
//
//        foreachInsert(n);
//
//        long t2 = System.currentTimeMillis();
//
//        statementBatch(n);

        long t3 = System.currentTimeMillis();

        preparedStatementBatch(n);

        long t4 = System.currentTimeMillis();

//        System.out.println("for循环的时间：" + (t2-t1) + "毫秒");
//        System.out.println("statement批处理的时间：" + (t3-t2) + "毫秒");
        System.out.println("preparedStatement批处理的时间:" + (t4-t3) + "毫秒");


    }




    // for循环
    public static void foreachInsert(int num) throws SQLException {

        for (int i = 0; i < num; i++) {

            Statement statement = connection.createStatement();

            String sql = "insert into stu values (null,'"+"foreach:" + i +"',null)";

            statement.executeUpdate(sql);

            statement.close();

        }
    }


    // statement进行批处理
    public static void statementBatch(int num) throws SQLException {

        // 创建Statement对象
        Statement statement = connection.createStatement();

        for (int i = 0; i < num; i++) {

            String sql = "insert into stu values (null,'"+"statement:" + i +"',null)";
            statement.addBatch(sql);
        }

        // 批量的发送SQL语句
        statement.executeBatch();

        statement.close();


    }



    // PreparedStatement批处理
    public static void preparedStatementBatch(int num) throws SQLException {

        // 1. 创建PreparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement("insert into stu values (null,?,?)");


        // 2. 设值
        for (int i = 0; i < num; i++) {

            preparedStatement.setString(1,"preparedStatement:" + i);
            preparedStatement.setString(2,null);

            preparedStatement.addBatch();

        }

        // 3. 执行SQL语句
        preparedStatement.executeBatch();


        preparedStatement.close();


    }


}
